﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using Ext.Net;
using System.Data;
using System.Xml;
using System.Xml.Xsl;
using System.Data.OleDb;
using System.Collections;
using System.Reflection;

/// <summary>
///ExtBaseRpt 的摘要说明
/// </summary>
public class ExtBaseData :Page
{
    protected MyDbHelper mydb = new MyDbHelper();
    protected DbHelper db = new DbHelper();
    BaseDataPage bp = new BaseDataPage();
    public string mid;
    public ExtBaseData()
	{
		//
		//TODO: 在此处添加构造函数逻辑
		//
	}

    protected void BuildGridPanel(GridPanel grid, string mid, string userid)
    {
        InsActLog(mid, "浏览");
        //动态创建用户网格
        grid.Title = mydb.DM_GetExecuteScalarObj("select modulename from sys_modules where mid=" + mid).ToString();
        grid.ColumnModel.Add(BuildColumns(mid, userid, "GridView1",null));
        //grid.StoreID = "Store1";
        if (X.IsAjaxRequest)
        {
            grid.Reconfigure();
        }
    }
    protected List<ColumnBase> BuildColumns(string mid, string userid, string gvname, FormPanel pn)
    {
        List<ColumnBase> list = new List<ColumnBase>();
        RowNumbererColumn nubcl = new RowNumbererColumn();
        nubcl.Text = "序号";
        nubcl.Width = 50;
        list.Add(nubcl);
        string sql = @"select mu_id,lindx,fieldname,fielddisplay,mu_visible,fieldwidth,datatype,gridfoot,showtype,fieldreadonly,isctrl,
                    datatype,showtype,ctrltype,ctrl_x,ctrl_y,iskeyid,ismust
                    from sys_moduleslayout a 
                    inner join sys_modulesuserlayout b on b.fid =lid 
                    inner join sys_modules m on m.mid = a.mid 
                    where m.parentid = " + mid + " and m.modulecode='"+gvname+"'"
                 + " and userid=" + Session["UserID"]+" order by lindx";

        DataSet ds = mydb.DM_GetDateSet(sql);
        if (ds != null)
        {
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                if (dr["mu_visible"].ToString().ToLower() == "true")
                {
                    //合计
                    if (dr["gridfoot"].ToString() == "1")
                    {
                        SummaryColumn cl = new SummaryColumn();
                        cl.Text = dr["fielddisplay"].ToString();
                        cl.DataIndex = dr["fieldname"].ToString();
                        cl.Width = Convert.ToInt32(dr["fieldwidth"]);
                        cl.SummaryType = SummaryType.Sum;
                        if (dr["showtype"].ToString() == "1")//单价
                            cl.Renderer.Fn = "price";
                        else if (dr["showtype"].ToString() == "2")//金额
                            cl.Renderer.Fn = "paymoney";
                        else if (dr["showtype"].ToString() == "3")//数量
                            cl.Renderer.Fn = "shuliang";
                        else if (dr["showtype"].ToString() == "4")//日期
                            cl.Renderer.Fn = "date";
                        else if (dr["showtype"].ToString() == "5")//百分比
                            cl.Renderer.Fn = "pctChange";
                        list.Add(cl);
                    }
                    else if (dr["datatype"].ToString() == "boolean")
                    {
                        CheckColumn cl = new CheckColumn();
                        cl.Text = dr["fielddisplay"].ToString();
                        cl.DataIndex = dr["fieldname"].ToString();
                        cl.Width = Convert.ToInt32(dr["fieldwidth"]);
                        list.Add(cl);
                    }
                    else
                    {
                        //cl.Renderer.Format = RendererFormat.UsMoney;
                        Column cl = new Column();
                        cl.Text = dr["fielddisplay"].ToString();
                        cl.DataIndex = dr["fieldname"].ToString();
                        cl.Width = Convert.ToInt32(dr["fieldwidth"]);
                        if (dr["showtype"].ToString() == "1")//单价
                            cl.Renderer.Fn = "price";
                        else if (dr["showtype"].ToString() == "2")//金额
                            cl.Renderer.Fn = "paymoney";
                        else if (dr["showtype"].ToString() == "3")//数量
                            cl.Renderer.Fn = "shuliang";
                        else if (dr["showtype"].ToString() == "4")//日期
                            cl.Renderer.Fn = "date";
                        else if (dr["showtype"].ToString() == "5")//百分比
                            cl.Renderer.Fn = "pctChange";
                        if (gvname == "明细")
                        {
                            if (dr["ctrltype"].ToString() == "0")
                            {
                                TextField text = new TextField();
                                text.ID = "txtd" + dr["fieldname"].ToString();
                                cl.Editor.Add(text);
                            }
                            else if (dr["ctrltype"].ToString() == "1")
                            {
                                DateField text = new DateField();
                                text.ID = "txtd" + dr["fieldname"].ToString();
                                cl.Editor.Add(text);
                            }
                        }
                        list.Add(cl);
                    }
                    if (dr["isctrl"].ToString().ToLower() == "true" && pn != null)
                    {
                        //文本型
                        if (dr["ctrltype"].ToString() == "0")
                        {
                            TextField text = new TextField();
                            text.ID = "txt" + dr["fieldname"].ToString();
                            text.Name = dr["fieldname"].ToString();
                            text.FieldLabel = dr["fielddisplay"].ToString();
                            text.Padding = 3;
                            text.LabelWidth = 80;
                            if (dr["ismust"].ToString() == "True")
                                text.AllowBlank = false;
                            //text.Width = Convert.ToInt32(dr["fieldwidth"]);
                            pn.Items.Add(text);
                        }
                        // 日期型
                        else if (dr["ctrltype"].ToString() == "1")
                        {
                            DateField text = new DateField();
                            text.ID = "txt" + dr["fieldname"].ToString();
                            text.Name = dr["fieldname"].ToString();
                            text.FieldLabel = dr["fielddisplay"].ToString();
                            //text.Width = Convert.ToInt32(dr["fieldwidth"]);
                            text.Padding = 3;
                            text.LabelWidth = 80;
                            if (dr["ismust"].ToString() == "True")
                                text.AllowBlank = false;
                            //text.Format="yyyy-MM-dd";
                            pn.Items.Add(text);
                        }
                        // 备注
                        else if (dr["ctrltype"].ToString() == "2")
                        {
                            TextArea text = new TextArea();
                            text.ID = "txt" + dr["fieldname"].ToString();
                            text.Name = dr["fieldname"].ToString();
                            text.FieldLabel = dr["fielddisplay"].ToString();
                            //text.Width = Convert.ToInt32(dr["fieldwidth"]);
                            text.Padding = 3;
                            text.LabelWidth = 80;
                            //text.Format="yyyy-MM-dd";
                            if (dr["ismust"].ToString() == "True")
                                text.AllowBlank = false;
                            pn.Items.Add(text);
                        }
                        // 选择框
                        else if (dr["ctrltype"].ToString() == "4")
                        {
                            Checkbox text = new Checkbox();
                            text.ID = "txt" + dr["fieldname"].ToString();
                            text.Name = dr["fieldname"].ToString();
                            text.BoxLabel = dr["fielddisplay"].ToString();
                            //text.Width = Convert.ToInt32(dr["fieldwidth"]);
                            text.Padding = 3;
                            text.LabelWidth = 80;
                            //text.Format="yyyy-MM-dd";
                            pn.Items.Add(text);
                        }
                    }
                }
            }
        }
        return list;
    }

    protected void QueryData(Store st,string mid,string sql)
    {
        this.StoreColumns(st, mid, "GridView1");
        DataSet ds = mydb.DM_GetDateSet(sql);
        st.DataSource = ds;
        st.DataBind();
    }
    protected DataSet QueryShop(string str)
    {
        return db.DM_GetDateSet("select shopid,shopcode,shopname from Bas_Shop where 1=1 " + str + " order by shopname");
    }
    protected string StrSplitParam(MultiCombo comShop)
    {
        //字符串处理(''M00001'',''M00002'')

        string str = "";
        foreach (Ext.Net.ListItem item in comShop.SelectedItems)
        {
            str += item.Value + ",";
        }
        if (str != "")
        {
            string rslt = "(";
            string[] items = str.Split(',');
            foreach (string val in items)
            {
                if (val != "")
                    rslt += "''" + val + "'',";
            }
            return rslt.TrimEnd(',') + ")";
        }
        else return "";
    }
    protected void StoreColumns(Store st,string mid,string gvname)
    {
        if (X.IsAjaxRequest)
        {
            st.RemoveFields();
        }

        //Model m = new Model();
        string sql = @"select mu_id,lindx,fieldname,fielddisplay,mu_visible,fieldwidth,datatype,iskeyid from sys_moduleslayout a 
                    inner join sys_modulesuserlayout b on b.fid =lid 
                    inner join sys_modules m on m.mid = a.mid 
                    where m.parentid = " + mid + " and m.modulecode='"+gvname+"'"
          + " and userid=" + Session["UserID"];
        DataSet ds = mydb.DM_GetDateSet(sql);
        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            if (dr["iskeyid"].ToString() == "True")
            {
                st.Model[0].IDProperty = dr["fieldname"].ToString();
            }
            if (dr["fielddisplay"].ToString() != "")
            {
                ModelField field = new ModelField();
                field.Name = dr["fieldname"].ToString();
                field.ServerMapping = dr["fieldname"].ToString();
                //if (dr["datatype"].ToString() == "datetime")
                //{
                //    field.Type = ModelFieldType.Date;
                //    field.DateFormat = "yyyy-MM-dd HH:mm:ss";
                //}
                this.AddField(st,field);
                //this.AddField(new ModelField(dr["fieldname"].ToString().ToLower()));
            }
        }
        st.RebuildMeta();
    }
    //创建store的数据字段
    private void AddField(Store st,ModelField field)
    {
        if (X.IsAjaxRequest)
        {
            st.AddField(field);
        }
        else
        {
            st.Model[0].Fields.Add(field);
        }
    }
    protected void ShowBox(string msg)
    {
        X.Msg.Alert("提示", msg).Show();
    }
    protected void ExtGridToExcel(string mid,string format, XmlNode xml,HttpResponse response,string title)
    {
        if (!CheckModulesRights(mid, "M_Export", "您没有权限操作此功能，请联系管理员！", ""))
        {
            return;
        }

        InsActLog(mid,"导出");
        response.Clear();

        switch (format)
        {
            case "xml":
                string strXml = xml.OuterXml;
                response.AddHeader("Content-Disposition", "attachment; filename=" + title + ".xml");
                response.AddHeader("Content-Length", strXml.Length.ToString());
                response.ContentType = "application/xml";
                response.Write(strXml);

                break;
            case "xls":
                response.ContentType = "application/vnd.ms-excel";
                response.AddHeader("Content-Disposition", "attachment; filename=" + title + ".xls");
                XslCompiledTransform xtExcel = new XslCompiledTransform();
                xtExcel.Load(Server.MapPath("Excel.xsl"));
                xtExcel.Transform(xml, null, Response.OutputStream);

                break;
            case "csv":
                response.ContentType = "application/octet-stream";
                response.AddHeader("Content-Disposition", "attachment; filename=" + title + ".csv");
                XslCompiledTransform xtCsv = new XslCompiledTransform();
                xtCsv.Load(Server.MapPath("Csv.xsl"));
                xtCsv.Transform(xml, null, Response.OutputStream);

                break;
        }

        response.End();
    }
    protected void InsActLog(string mid,string acname)
    {
        bp.InsertActionLog(Session["UserID"].ToString(), mid, acname, DateTime.Now.ToString());
    }
    //检查导出的权限
    protected bool CheckModulesRights(string mid,string action,string msg,string url)
    {
        string isBrowse = mydb.DM_GetExecuteScalarObj("select "+action+" from sys_modulerights where mid=" + mid
            + " and userid=" + Session["UserID"]).ToString();
        if (string.IsNullOrEmpty(isBrowse))
        {
            ShowBox(msg);
            return false;
        } if (isBrowse.ToLower() == "-1")
        {
            ShowBox(msg);
            return false;
        }
        if (isBrowse.ToLower() == "false")
        {
            ShowBox(msg);
            return false;
        }
        return true;
    }
    public DataSet ExcelDataSource(string filepath, string sheetname)
    {
        string strConn;
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
        OleDbConnection conn = new OleDbConnection(strConn);
        OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
        DataSet ds = new DataSet();
        oada.Fill(ds);
        conn.Close();
        return ds;
    }
    //获得Excel中的所有sheetname。
    public ArrayList ExcelSheetName(string filepath)
    {
        ArrayList al = new ArrayList();
        string strConn;
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
        OleDbConnection conn = new OleDbConnection(strConn);
        conn.Open();
        DataTable sheetNames = conn.GetOleDbSchemaTable
        (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
        conn.Close();
        foreach (DataRow dr in sheetNames.Rows)
        {
            al.Add(dr[2]);
        }
        return al;
    }
    protected bool DataSetToExcel(DataSet dataSet, string fileName, bool isShowExcle)
    {
        DataTable dataTable = dataSet.Tables[0];
        int rowNumber = dataTable.Rows.Count;//不包括字段名 
        int columnNumber = dataTable.Columns.Count;
        int colIndex = 0;

        if (rowNumber == 0)
        {
            ShowBox("没有任何数据可以导入到Excel文件！");
            return false;
        }

        //建立Excel对象 
        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        //excel.Application.Workbooks.Add(true); 
        Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
        Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
        excel.Visible = isShowExcle;
        //Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1]; 
        worksheet.Name = fileName;
        Microsoft.Office.Interop.Excel.Range range;

        //生成字段名称 
        foreach (DataColumn col in dataTable.Columns)
        {
            colIndex++;
            excel.Cells[1, colIndex] = col.ColumnName;
        }

        object[,] objData = new object[rowNumber, columnNumber];

        for (int r = 0; r < rowNumber; r++)
        {
            for (int c = 0; c < columnNumber; c++)
            {
                objData[r, c] = dataTable.Rows[r][c];
            }
            //Application.DoEvents(); 
        }

        // 写入Excel 
        range = worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]);
        //range.NumberFormat = "@";//设置单元格为文本格式 
        range.Value2 = objData;
        worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, 1]).NumberFormat = "yyyy-m-d h:mm";

        //string fileName = path + "\\" + DateTime.Now.ToString().Replace(':', '_') + ".xls"; 
        string path = Server.MapPath("/UploadFile/") + fileName+".xls";
        workbook.SaveAs(path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

        try
        {
            workbook.Saved = true;
            excel.UserControl = false;
            //excelapp.Quit(); 
        }
        catch (Exception exception)
        {
            ShowBox(exception.Message);
        }
        finally
        {
            workbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Missing.Value, Missing.Value);
            excel.Quit();
        }

        //if (isShowExcle) 
        //{ 
        //    System.Diagnostics.Process.Start(fileName); 
        //} 
        return true;
    } 

}